
import com.sun.mail.smtp.SMTPTransport;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;
import java.util.Random;
import java.util.UUID;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Node;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Patrick
 */
public class UserManager {

    private static String LETTERS = "abcdefghigklimopqrstuvwxyz1234567890";

    public enum Status {

        NOTFOUND,
        WRONGPASSWORD,
        SUCCESS
    }

    public static User getUser(Connection con, String email, String userPassword) throws SQLException {
        User user = null;
        String sql = "SELECT id,username,password from crombusers where email=? limit 1";
        PreparedStatement stat = con.prepareCall(sql);
        stat.setString(1, email);
        ResultSet rs = stat.executeQuery();
        //There should be only one row...
        while (rs.next()) {
            user = new User();
            user.userID = rs.getInt("id");
            user.userName = rs.getString("username");
            user.email=email;
            String pass = rs.getString("password");
            if (!pass.equals(userPassword)) {
                user.userID=-2;
            }
        }
        rs.close();
        stat.close();
        return user;
    }
    private static boolean isDuplicateUserName(Connection con, String userName,String email) throws SQLException{
        boolean retValue = false;
        String sql = "SELECT username FROM crombusers WHERE username=? AND email <> ?";
        PreparedStatement stat = con.prepareCall(sql);
        stat.setString(1, userName);
        stat.setString(2, email);
        ResultSet rs = stat.executeQuery();
        retValue = rs.next();
        rs.close();
        stat.close();
        return retValue;
    }

    private static User addUser(Connection con, String userName, String userPassword, String email) throws SQLException {
        if (userName.equals("New User")){
            userName = "User-"+UUID.randomUUID().toString();
        }
        if (isDuplicateUserName(con, userName, email)) {
            User u = new User();
            u.userID=-2;
            return u;
        }
        String sql = "INSERT INTO crombusers (username,password,email) VALUES(?,?,?)";
        PreparedStatement stat = con.prepareCall(sql);
        stat.setString(1, userName);
        stat.setString(2, userPassword);
        stat.setString(3, email);
        stat.execute();
        stat.close();
        return getUser(con, email, userPassword);
    }

    private static User updateUser(Connection con, String userName, String userPassword,String email) throws SQLException {
        if (userName.equals("New User")){
            userName = "User-"+UUID.randomUUID().toString();
        }
        if (isDuplicateUserName(con, userName, email)) {
            User u = new User();
            u.userID=-2;
            return u;
        }
        String sql = "UPDATE crombusers set password=?, username=? where email=?";
        PreparedStatement stat = con.prepareCall(sql);
        stat.setString(1, userPassword);
        stat.setString(2, userName);
        stat.setString(3, email);
        stat.execute();
        stat.close();
        return getUser(con, email, userPassword);
    }

    public static User updateCrombUser(Connection con, String username, String email,String host, int port, String systemUser, String systemPassword) throws MessagingException, SQLException {
        User user = getUser(con, email, "");
        String password = createRandomPassword();
        
        if (user!=null && user.userID == -2) {
            user = updateUser(con, username, password,email);
        } else {
            user = addUser(con, username, password, email);
        }
        if (user!=null && user.userID >=0){
            sendPasswordEmail(username, password, email, host, port, systemUser, systemPassword);
        }
        return user;
    }

    private static void sendPasswordEmail(String username, String password, String email, String host, int port, String systemUser, String systemPassword) throws MessagingException {
        Properties props = new Properties();
        props.put("mail.smtp.host", host);
        props.put("mail.smtp.port", port);
        props.put("mail.smtp.auth", "true");

        Session session = Session.getInstance(props, null);
        Message msg = new MimeMessage(session);
        msg.setFrom(new InternetAddress(systemUser));
        msg.setRecipients(Message.RecipientType.TO,
                InternetAddress.parse(email, false));
        msg.setSubject("Your Crombz Credentials");
        String text = "Hi, this is CrombMaster from Crombz.com\n";
        text += "\nYou've requested a password:";
        text += "\nUsername: " + username;

        text += "\nPassword: " + password;
        text += "\n\nVisit Crombz.com and use these informations to login.";
        text += "\n\nEnjoy and share with your friends!\n\nCrombMaster";
        msg.setText(text);
        msg.setHeader("X-Mailer", "Crombz.com");
        msg.setSentDate(new Date());
        SMTPTransport t = (SMTPTransport) session.getTransport("smtp");
        try {
            t.connect(host, port, systemUser, systemPassword);
            t.sendMessage(msg, msg.getAllRecipients());
        } finally {
            System.out.println("Response: "
                    + t.getLastServerResponse());
            t.close();
        }
    }

    private static ArrayList<User> getTopCrombers(Connection con) throws SQLException{
        ArrayList<User> list = new ArrayList();
        String sql = "SELECT username,count(userid) postnb FROM crombusers inner join cromb on crombusers.id=cromb.userid group by userid order by postnb desc limit 10";
        Statement stat = con.createStatement();
        ResultSet rs = stat.executeQuery(sql);
        while (rs.next()){
            User user = new User();
            user.userName = rs.getString("username");
            user.totalPost = rs.getInt("postnb");
            list.add(user);
        }
        rs.close();
        stat.close();
        return list;
    }
    public static String createRandomPassword() {
        String pass = "";
        Random rand = new Random(System.currentTimeMillis());
        for (int i = 0; i < 8; i++) {
            int index = rand.nextInt(LETTERS.length());
            pass += LETTERS.substring(index, index + 1);
        }
        return pass;
    }
    public static String getXMLTopCrombers(Connection con) throws ParserConfigurationException, TransformerConfigurationException, SQLException, TransformerException {
        Document doc = getBaseDocument();
        Node root = doc.getElementsByTagName("crombz").item(0);
        ArrayList<User> list = getTopCrombers(con);
        for (User u : list) {
            Node user = doc.createElement("user");
            Node username = doc.createAttribute("username");
            Node totalPost = doc.createAttribute("totalpost");
            username.setNodeValue(u.userName);
            totalPost.setNodeValue(u.totalPost+"");
            user.getAttributes().setNamedItem(username);
            user.getAttributes().setNamedItem(totalPost);
            root.appendChild(user);
        }
        return getXML(doc);
    }
    private static Document getBaseDocument() throws ParserConfigurationException {
        Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
        doc.appendChild(doc.createElement("crombz"));
        return doc;
    }
    private static String getXML(Document doc) throws TransformerConfigurationException, TransformerException {
        TransformerFactory tf = TransformerFactory.newInstance();
        Transformer transformer = tf.newTransformer();
        transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
        StringWriter writer = new StringWriter();
        transformer.transform(new DOMSource(doc), new StreamResult(writer));
        return writer.getBuffer().toString().replaceAll("\n|\r", "");
    }
}
